package com.cjy.db;

import java.sql.*;

public class BaseDao {
//	public final static String DRIVER = "com.microsoft.sqlserver.jdbc.SQLServerDriver"; // 数据库驱动
//	public final static String URL = "jdbc:sqlserver://localhost:1433;DataBaseName=db_17java"; // url
//	public final static String DBNAME = "sa"; // 数据库用户名
//	public final static String DBPASS = "sa"; // 数据库密码

	public final static String DRIVER = "com.mysql.cj.jdbc.Driver"; // 数据库驱动
	public final static String URL = "jdbc:mysql://localhost:3306/db_book"; // url
	public final static String DBNAME = "root"; // 数据库用户名
	public final static String DBPASS = "abc22456"; // 数据库密码
//	public static String DRIVER = null;
//	public static String URL = null;
//	public static String DBNAME = null;
//	public static String DBPASS = null;
//	static{
//		Properties properties = null;
//		InputStream inputStream = null;
//		try {
//			inputStream = BaseDao.class.getClassLoader().getResourceAsStream("jdbc.properties");
//			properties = new Properties();
//			properties.load(inputStream);
//			DRIVER = properties.getProperty("DRIVER");
//			URL = properties.getProperty("URL");
//			DBNAME = properties.getProperty("DBNAME");
//			DBPASS = properties.getProperty("DBPASS");
//		} catch (Exception e) {
//			throw new RuntimeException("数据库属性加载异常!");
//		} finally{
//			try {
//				if (inputStream != null) {
//					inputStream.close();
//					inputStream = null;
//				}
//			} catch (IOException e) {
//				e.printStackTrace();
//			}
//		}
//	};
	/**
	 * 得到数据库连接
	 * 
	 * @throws ClassNotFoundException
	 * @throws SQLException
	 * @return 数据库连接
	 */
	public Connection getConn() throws ClassNotFoundException, SQLException {
		Class.forName(DRIVER); // 注册驱动
		Connection conn = DriverManager.getConnection(URL, DBNAME, DBPASS); // 获得数据库连接
		return conn; // 返回连接
	}
   
	/**
	 * 释放资源
	 * 
	 * @param conn
	 *            数据库连接
	 * @param pstmt
	 *            PreparedStatement对象
	 * @param rs
	 *            结果集
	 */
	public void closeAll(Connection conn, PreparedStatement pstmt, ResultSet rs) {
		/* 如果rs不空，关闭rs */
		if (rs != null) {
			try {
				rs.close();
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
		/* 如果pstmt不空，关闭pstmt */
		if (pstmt != null) {
			try {
				pstmt.close();
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
		/* 如果conn不空，关闭conn */
		if (conn != null) {
			try {
				conn.close();
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
	}

	/**
	 * 执行SQL语句，可以进行增、删、改的操作，不能执行查询
	 * 
	 * @param preparedSql
	 *            预编译的 SQL 语句
	 * @param param
	 *            预编译的 SQL 语句中的‘？’参数的字符串数组
	 * @return 影响的条数
	 */
	public int executeSQL(String preparedSql, String[] param) {
		Connection conn = null;
		PreparedStatement pstmt = null;
		int num = 0;

		/* 处理SQL,执行SQL */
		try {
			conn = getConn(); // 得到数据库连接
			pstmt = conn.prepareStatement(preparedSql); // 得到PreparedStatement对象
			if (param != null) {
				for (int i = 0; i < param.length; i++) {
					pstmt.setString(i + 1, param[i]); // 为预编译sql设置参数
				}
			}
			num = pstmt.executeUpdate(); // 执行SQL语句
		} catch (ClassNotFoundException e) {
			e.printStackTrace(); // 处理ClassNotFoundException异常
		} catch (SQLException e) {
			e.printStackTrace(); // 处理SQLException异常
		} finally {
			closeAll(conn, pstmt, null); // 释放资源
		}
		return num;
	}
	public ResultSet executeQuery(String preparedSql){
		Connection conn = null;
		PreparedStatement pstmt = null;
		ResultSet rs = null;
		try {
			conn = getConn();
			pstmt = conn.prepareStatement(preparedSql);			
			rs = pstmt.executeQuery();
		} catch (ClassNotFoundException e) {
			e.printStackTrace(); // 处理ClassNotFoundException异常
		} catch (SQLException e) {
			e.printStackTrace(); // 处理SQLException异常
		} finally {
			// closeAll(conn,pstmt,null); // 释放资源
		}
		return rs;
	}

	public ResultSet executeQuery(String preparedSql, String[] param) {
		Connection conn = null;
		PreparedStatement pstmt = null;
		ResultSet rs = null;
		try {
			conn = getConn();
			pstmt = conn.prepareStatement(preparedSql);
			if (param != null) {
				for (int i = 0; i < param.length; i++) {
					pstmt.setString(i + 1, param[i]); // 为预编译sql设置参数
				}
			}
			rs = pstmt.executeQuery();
		} catch (ClassNotFoundException e) {
			e.printStackTrace(); // 处理ClassNotFoundException异常
		} catch (SQLException e) {
			e.printStackTrace(); // 处理SQLException异常
		} finally {
			// closeAll(conn,pstmt,null); // 释放资源
		}
		return rs;
	}

	public ResultSet executeQuery(String preparedSql, Integer[] param) {
		Connection conn = null;
		PreparedStatement pstmt = null;
		ResultSet rs = null;
		try {
			conn = getConn();
			pstmt = conn.prepareStatement(preparedSql);
			if (param != null) {
				for (int i = 0; i < param.length; i++) {
					pstmt.setInt(i + 1, param[i]); // 为预编译sql设置参数
				}
			}
			rs = pstmt.executeQuery();
		} catch (ClassNotFoundException e) {
			e.printStackTrace(); // 处理ClassNotFoundException异常
		} catch (SQLException e) {
			e.printStackTrace(); // 处理SQLException异常
		} finally {
			// closeAll(conn,pstmt,null); // 释放资源
		}
		return rs;
	}
   
	/*public static void main(String[] args) {
		BaseDao b = new BaseDao();
		System.out.print(b);
		String preparedSql = "select * from tb_book limit ?, ?";
		String[] param = new String[] {
                String.valueOf(0),
				String.valueOf(5),
		};
		ResultSet rs = b.executeQuery(preparedSql, param);
		try {
			while (rs.next()) {
				System.out.print(rs.getInt("id"));
			}
		} catch (Exception e) {
			e.printStackTrace();
		}
	}*/
}
